﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace UdrinkWater_Project.Admin
{
    public partial class StockWarnning : System.Web.UI.Page
    {
        string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        public static int dtConut = 0;
        
        
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dtWStock = GetStockWarn();
            DataTable dtAllProducName = GetAllIDProduct();
            DataTable InStock = new DataTable();
            DataTable OutStock = new DataTable();
            DataTable dtSumAll = new DataTable();
            int WarnStock = Int32.Parse(dtWStock.Rows[0]["StockWarn"].ToString());
            for (int i = 0; i < dtAllProducName.Rows.Count; i++)
            {
                InStock = GetInstock(dtAllProducName.Rows[i]["Product_ID"].ToString());
                OutStock = GetOutstock(dtAllProducName.Rows[i]["Product_ID"].ToString());
                int NumIN = 0;
                int NumOUT = 0;
                for (int j = 0; j < InStock.Rows.Count; j++)
                {

                    NumIN = NumIN + Convert.ToInt32(InStock.Rows[j]["Product_Count"].ToString());
                }
                for (int k = 0; k < OutStock.Rows.Count; k++)
                {
                    NumOUT = NumOUT + Convert.ToInt32(OutStock.Rows[k]["Product_Count"].ToString());
                }

                if (dtSumAll != null && dtSumAll.Rows.Count == 0 && (NumIN - NumOUT) <= WarnStock)
                {
                    DataRow drStockRow = null;
                    dtSumAll = new DataTable();
                    dtSumAll.Columns.Add(new DataColumn("Product_ID", typeof(string)));
                    dtSumAll.Columns.Add(new DataColumn("Product_Name", typeof(string)));
                    dtSumAll.Columns.Add(new DataColumn("Unit", typeof(string)));
                    dtSumAll.Columns.Add(new DataColumn("Count", typeof(int)));



                    drStockRow = dtSumAll.NewRow();
                    drStockRow["Product_ID"] = dtAllProducName.Rows[i]["Product_ID"].ToString();
                    drStockRow["Product_Name"] = dtAllProducName.Rows[i]["Product_Name"].ToString();
                    drStockRow["Unit"] = dtAllProducName.Rows[i]["Unit_Name"].ToString();
                    drStockRow["Count"] = NumIN - NumOUT;

                    dtSumAll.Rows.Add(drStockRow);
                    gvStock.DataSource = dtSumAll;
                    gvStock.DataBind();
                    dtConut = 1;
                }
                else if (dtSumAll != null && dtSumAll.Rows.Count > 0 && (NumIN - NumOUT) <= WarnStock)
                {

                    DataRow drStockRow = dtSumAll.NewRow();
                    drStockRow["Product_ID"] = dtAllProducName.Rows[i]["Product_ID"].ToString();
                    drStockRow["Product_Name"] = dtAllProducName.Rows[i]["Product_Name"].ToString();
                    drStockRow["Unit"] = dtAllProducName.Rows[i]["Unit_Name"].ToString();
                    drStockRow["Count"] = NumIN - NumOUT;
                    dtSumAll.Rows.Add(drStockRow);
                    gvStock.DataSource = dtSumAll;
                    gvStock.DataBind();



                }
            }
           
        }
        

        private DataTable GetStockWarn()
        {
            string sqltext = @"SELECT [id]
                                ,[StockWarn]
                                FROM TB_StockWarn
                             WHERE id=1";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        private DataTable GetOutstock(string Product_ID)
        {
            string sqltext = @"SELECT        Product_ID, Product_Count
                               FROM          TB_StockProductOUT
                               WHERE         Product_ID = '" + Product_ID + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        private DataTable GetInstock(string Product_ID)
        {
            string sqltext = @"SELECT        Product_ID, Product_Count
                               FROM          TB_StockProductIN
                               WHERE         Product_ID = '" + Product_ID + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        private DataTable GetAllIDProduct()
        {
            string sqltext = @"SELECT DISTINCT       TB_StockProductIN.Product_ID, TB_Product.Product_name, TB_Unit_Category.Unit_Name
                                FROM            TB_StockProductIN LEFT JOIN
                         TB_Product ON TB_StockProductIN.Product_ID = TB_Product.Product_ID LEFT JOIN
                         TB_Unit_Category ON TB_Product.Unit = TB_Unit_Category.Unit_ID
                         UNION
                         SELECT DISTINCT       TB_StockProductOUT.Product_ID, TB_Product.Product_name, TB_Unit_Category.Unit_Name
                                FROM            TB_StockProductOUT LEFT JOIN
                         TB_Product ON TB_StockProductOUT.Product_ID = TB_Product.Product_ID LEFT JOIN
                         TB_Unit_Category ON TB_Product.Unit = TB_Unit_Category.Unit_ID";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }

        protected void ASPxButton1_Click(object sender, EventArgs e)
        {
            ASPxGridViewExporter1.WritePdfToResponse();
        }
    }
}